﻿/******************************* 
** 作者： 斯泰尔 
** 时间： 2021-08-25 09:42:16 
** 版本: V1.0.0 
** CLR: 4.0.30319.42000 
** GUID: db762c22-d20a-443d-b0b5-433a2e1e64bf 
** 描述： SQLiteHelper类 
*******************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Configuration;
using System.Data;

namespace AliSMS
{
    /// <summary>
    /// MsSqlHelper 的摘要说明
    /// </summary>
    public class SQLiteHelper
    {
        private SQLiteConnection conn;

        private string connection = "Data Source=Data.db";
        public SQLiteHelper()
        {
            try
            {
                Conn = new SQLiteConnection(connection);
            }
            catch (Exception ex)
            {
                // Init.WriteLog(ex.ToString(), true);
            }
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }


        #region 最新数据表信息显示事件
        /// <summary>
        /// 最新数据表信息显示事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public DataTable SqliteInfo()
        {
            using (SQLiteConnection conn = new SQLiteConnection(connection))
            {
                conn.Open();
                DataTable schemaTable = conn.GetSchema("TABLES");
                // 移除数据表中特定的列
                //schemaTable.Columns.Remove("TABLE_CATALOG");
                //schemaTable.Columns.Remove("SYSTEM_TABLE");
                
                // 设定特定列的序号
                schemaTable.Columns["TABLE_NAME"].SetOrdinal(1);
                return schemaTable;
            }
        }
        public DataTable SqliteInfo(String connection)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connection))
            {
                conn.Open();
                DataTable schemaTable = conn.GetSchema("TABLES");
                // 移除数据表中特定的列
                schemaTable.Columns.Remove("TABLE_CATALOG");
                // 设定特定列的序号
                schemaTable.Columns["TABLE_NAME"].SetOrdinal(1);
                return schemaTable;
            }
        }
        #endregion

        /// <summary>  
        /// 执行不带参数的SQL语句  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public int ExecuteSql(String Sqlstr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connection))
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                int num = Convert.ToInt16(cmd.ExecuteNonQuery());
                conn.Close();
                return (num == 0) ? 0 : num;
                //return 1;
            }
        }

        public int ExecuteSql(String Sqlstr, String mssqlConnect)
        {
            using (SQLiteConnection conn = new SQLiteConnection(mssqlConnect))
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                int num = Convert.ToInt16(cmd.ExecuteNonQuery());
                conn.Close();
                return (num == 0) ? 0 : num;
                //return 1;
            }
        }

        /// <summary>  
        /// 返回DataReader  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public SQLiteDataReader XExecuteReader(String Sqlstr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connection))
            {
                SQLiteCommand cmd = new SQLiteCommand(Sqlstr, conn);
                conn.Open();
                SQLiteDataReader dr = cmd.ExecuteReader();
                conn.Close();
                return dr;
            }
        }

        public SQLiteDataReader XExecuteReader(String Sqlstr, String mssqlConnect)
        {
            using (SQLiteConnection conn = new SQLiteConnection(mssqlConnect))
            {
                SQLiteCommand cmd = new SQLiteCommand(Sqlstr, conn);
                conn.Open();
                SQLiteDataReader dr = cmd.ExecuteReader();
                conn.Close();
                return dr;
            }
        }
        public SQLiteDataReader ExecuteReader(String Sqlstr)
        {
            SQLiteConnection conn = new SQLiteConnection(connection);//返回DataReader时,是不可以用using()的  
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
                //sconn.Close();
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }
        public SQLiteDataReader ExecuteReader(String Sqlstr, String mssqlConnect)
        {
            SQLiteConnection conn = new SQLiteConnection(mssqlConnect);//返回DataReader时,是不可以用using()的  
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
                //sconn.Close();
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }

        /// <summary>  
        /// 执行SQL语句并返回数据表  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public DataTable ExecuteDt(String Sqlstr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connection))
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        public DataTable ExecuteDt(String Sqlstr, String mssqlConnect)
        {
            using (SQLiteConnection conn = new SQLiteConnection(mssqlConnect))
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>  
        /// 执行SQL语句并返回DataSet  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public DataSet ExecuteDs(String Sqlstr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connection))
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回DataSet 
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="mssqlConnect">数据库链接</param>
        /// <returns></returns>
        public DataSet ExecuteDs(String Sqlstr, String mssqlConnect)
        {
            using (SQLiteConnection conn = new SQLiteConnection(mssqlConnect))
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回一个文本值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public string GetString(string Sqlstr)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }
        /// <summary>
        /// 与GetString(string Sqlstr, string field)区分
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="mssqlConnect"></param>
        /// <returns></returns>
        public string GetStringX(string Sqlstr, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr, mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }
        /// <summary>
        /// 执行SQL语句并返回一个文本值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="i">第i个字段</param>
        /// <returns></returns>
        public string GetString(string Sqlstr, int i)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][i].ToString();
            }
            return "";
        }
        public string GetString(string Sqlstr, int i, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr, mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][i].ToString();
            }
            return "";
        }
        /// <summary>
        /// 执行SQL语句并返回一个文本值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="field">字段名</param>
        /// <returns></returns>
        public string GetString(string Sqlstr, string field)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][field].ToString();
            }
            return "";
        }
        public string GetString(string Sqlstr, string field, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr, mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][field].ToString();
            }
            return "";
        }
        /// <summary>
        /// 执行SQL语句并返回值是否存在 
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public bool Getbool(string Sqlstr)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public bool Getbool(string Sqlstr, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr, mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }


        public SQLiteConnection Conn
        {
            get
            {
                return conn;
            }

            set
            {
                conn = value;
            }
        }
    }
}